﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Models
{
    public class FavoriteModel
    {
        private string connectionString;
        private SqlConnection connect;

        public FavoriteModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public DataTable getTopFeature()
        {
            DataTable result = new DataTable();
            try
            {
                connect.Open();
                string selectSQL = "SELECT DISTINCT Top 3 F.book_id,count(F.book_id) FROM Favorite_book F INNER JOIN Book B ON F.book_id = B.book_id WHERE B.status != 0 GROUP BY F.book_id Order By count(F.book_id) DESC";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(reader);
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int getFavorite(int ID)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total_book FROM Favorite_book WHERE book_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);
                
                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int insertFavorite(int userID,int bookID)
        {
            int result = 0;
            try
            {
                connect.Open();

                string selectSQL = "SELECT count(*) as total_book FROM Favorite_book WHERE book_id = " + bookID.ToString() + " AND user_id = " + userID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_book"]);
                reader.Close();
                if (result != 0) return -1;

                selectSQL = "INSERT INTO Favorite_book (user_id,book_id) VALUES ('" + userID.ToString() + "','" + bookID.ToString() + "')";
                cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -2;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
    }
}
